import string

import pymysql

#操作数据库：1.链接2.建立游标3.将对应的sql语句封装4，利用游标变量执行sql语句5.commit提交6.关闭


#连接数据库
def connect_tosql():
    conn = pymysql.connect(host="103.42.30.121",
                           port=3306,
                           db='test',
                           user="remote_user",
                           password="remote_user",
                           charset='utf8')
    return conn


#处理原始数据
def get_requestData(rqData):
    strs = rqData.split(' ')
    #print(strs)
    #print(len(strs))
    for i in range(0, len(strs)):
        str = "%"
        rq = strs[i]
        #print(rq)
        #print()
        for j in range(0, len(rq)):
            str = str + rq[j] + "%"
        strs[i] = str
        #print (strs)
    return strs


#*按照标题查询*
#opt=1：搜索结果按照发布时间排序 opt=2搜索时间按照执行时间排序
#支持双关键字搜索，以空格分隔关键字，首个关键字优先级较大，搜索逻辑为交集
def executeSelectByTitle(str, opt=1):
    strs = get_requestData(str)
    print(strs)
    print((len(strs)))
    conn = connect_tosql()
    print(conn)
    cls = conn.cursor()
    sqlsql="SELECT * FROM Report WHERE id in (SELECT id FROM Report WHERE title LIKE %s) and title LIKE %s order by " \
            "appearDate DESC"
    sqlsql2="SELECT * FROM Report WHERE id in (SELECT id FROM Report WHERE title LIKE %s) and title LIKE %s order by " \
            "startDate DESC"
    sql1 = "SELECT * FROM Report WHERE title LIKE %s order by appearDate DESC "
    sql2 = "SELECT * FROM Report WHERE title LIKE %s order by startDate DESC "

    if opt == 1:
        if len(strs) > 1:
            cls.execute(sqlsql, [strs[0], strs[1]])
        else:
            cls.execute(sql1, [strs[0]])

    elif opt == 2:
        if len(strs) > 1:
            cls.execute(sqlsql2, [strs[0], strs[1]])
        else:
            cls.execute(sql2, [strs[0]])

    results = cls.fetchall()
    conn.close()
    for i in results:
        print(i)
    return results


executeSelectByTitle("环境 海洋")
